IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('UFN_GetSTPDepartmentHirachy')
          AND TYPE IN ('F','FN'))
   DROP FUNCTION UFN_GetSTPDepartmentHirachy
GO

/****** Object:  UserDefinedFunction [dbo].[UFN_GetSTPDepartmentHirachy]    Script Date: 10/20/2010 15:28:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		AKE
-- Create date: 1:34 PM 8/5/2010
-- =============================================
CREATE FUNCTION [dbo].[UFN_GetSTPDepartmentHirachy]
(
	@department_code int
)
RETURNS nvarchar(max)
AS
BEGIN
	
	
	--select * from stp_department
	
	--declare @department_code int
	--set		@department_code = 2
	
	----================================================
	----Init variables
	----================================================
	--declare @displayText nvarchar(max)
	--declare @department_parent int
	----set		@department_parent = 1;
	
	
	----================================================
	----Assign init id
	----================================================
	--select	@displayText = department_name ,
	--		@department_parent = department_parent
	--from	stp_department
	--where	department_code = @department_code;
	
	
	----================================================
	----Concat text to display by finding parent_code
	----================================================
	--while (@department_parent is not null) begin

	--	select	@displayText = ' > ' + department_name ,
	--			@department_parent = department_parent
	--	from	stp_department
	--	where	department_code = @department_parent;
	
	--end
	
	--return @displayText;
	
	
	declare @tbl table(
		department_code			int,
		department_name			nvarchar(max),
		department_parent		int,
		seq_no					int
	)
	;WITH hc_data (department_code,department_name,department_parent,seq_no)  
	AS  
	( 
	
		select	department_code , department_name , department_parent , seq_no = 1
		from	stp_department
		where	department_code = @department_code

		UNION ALL 
		
		select	p.department_code , p.department_name , p.department_parent ,seq_no + 1
		from	stp_department as p
		join	hc_data as s on p.department_code = s.department_parent
		
	)  
	


	
	insert into @tbl (department_code,department_name,department_parent,seq_no)
	SELECT  
		department_code,		
		department_name,			
		department_parent,		
		seq_no		
	FROM hc_data			


	declare @loop int
	declare @max int
	select	@loop = count(*) ,
			@max = count(*)
	from	@tbl

	declare @output nvarchar(max)
	set @output = ''
	
	while @loop <> 0 begin
	
		if @loop = @max begin
			select  @output = department_name
			from	@tbl
			where	seq_no = @loop
		end
		else begin
            -- Modified by Boonyarit Yingvoragan
            -- Modified date 18/10/2553
			select  @output = '&nbsp;&nbsp;&nbsp;' + department_name
			from	@tbl
			where	seq_no = @loop
		end
	

		set @loop = @loop - 1;
	
	end
	
	return @output

END

GO


